# 导入:
from sqlalchemy import Column, String, create_engine, Integer, Date, distinct, Text, func, text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类:
Base = declarative_base()

# 初始化数据库连接:
engine = create_engine('mysql+mysqlconnector://root:123456@localhost:3306/pythonstudy')
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)

# 创建session对象:
session = DBSession()


def db_query_diaries():
    """
    查询用户的日记
    :return:
    """
    res = session.query(Diary).all()
    res_list = []
    for item in res:
        res_list.append(item.__str__())
    session.close()
    return res_list


# 定义Diary对象:
class Diary(Base):
    # 表的名字:
    __tablename__ = 'yb_diary'

    # 表的结构:
    did = Column(Integer, primary_key=True, autoincrement=True)
    content = Column(String(255), nullable=False)
    addtime = Column(String(255))
    uid = Column(Integer, nullable=False)

    # 便于查询数据库后转换为json数据
    def __str__(self):
        map_str = {'did': self.did, 'content': self.content, 'addtime': self.addtime, 'uid': self.uid}
        return map_str


def db_insert_article(article):
    """
    根据文章对象，插入文章
    :param article:
    :return:
    """
    res = session.add(article)
    session.commit()
    return res


def db_query_articles(page=1, hot=0):
    """
    查询文章（默认每次只查询5条记录，默认不考虑热度查询）
    :param page:
    :param hot:
    :return:
    """
    aRight = page * 5
    aLeft = (page - 1) * 5
    res = []
    if hot != 0:  # 按照热度降序
        res = session.query(Article).order_by(Article.views.desc()).slice(aLeft, aRight).all()
    else:  # 按照时间，降序
        res = session.query(Article).order_by(Article.addtime.desc()).slice(aLeft, aRight).all()
    res_list = []
    for item in res:
        res_list.append(item.__str__())
    session.close()
    return res_list


def db_query_article(aid):
    """
    根据文章id查询文章内容
    :param aid:
    :return:
    """
    article = session.query(Article).filter_by(aid=aid).first()
    res_list = [article.__str__()]
    session.close()
    return res_list


def db_query_keywords(num):
    """
    查询文章的关键字(只需要limit五条记录，还要去掉重复的)
    :return:
    """
    if num == 5:
        res = session.query(distinct(Article.keyword)).limit(num).all()
    else:
        res = session.query(distinct(Article.keyword)).all()
    session.close()
    return res


def db_query_many_keywords():
    """
    查询字段出现次数最多的内容
    :return:
    """
    sql = 'SELECT DISTINCT keyword,count(*) AS count FROM yb_article GROUP BY keyword ORDER BY count DESC LIMIT 6'
    res = session.execute(text(sql))
    res_list = []
    for item in res:
        new_item = [item[0], item[1]]
        res_list.append(new_item)
    session.close()
    return res_list


# 定义Article对象
class Article(Base):
    # 表的名字
    __tablename__ = 'yb_article'

    # 表的结构:
    aid = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(150), nullable=False)
    keyword = Column(String(100))
    desc = Column(String(255))
    content = Column(Text)
    pic = Column(String(150))
    source = Column(String(45))
    addtime = Column(String(10))
    comments = Column(Integer)
    views = Column(Integer)
    ischeck = Column(Integer)
    istop = Column(Integer)

    # 便于查询数据库后转换为json数据
    def __str__(self):
        map_str = {'aid': self.aid, 'title': self.title, 'keyword': self.keyword, 'desc': self.desc,
                   'content': self.content, 'pic': self.pic, 'source': self.source, 'addtime': self.addtime,
                   'comments': self.comments, 'views': self.views, 'ischeck': self.ischeck, 'istop': self.istop}
        return map_str


#
def db_login_user(phonenum, passwd):
    """
    根据用户电话和密码，判断是否存在用户，进而登录
    :param phonenum:
    :param passwd:
    :return:
    """
    user = session.query(User).filter(User.phonenum == phonenum, User.passwd == passwd).one()
    res_list = [user.__str__()]
    session.close()
    return res_list


# 定义User对象
class User(Base):
    # 表的名字
    __tablename__ = 'yb_user'

    # 表的结构:
    uid = Column(Integer, primary_key=True, autoincrement=True)
    phonenum = Column(String(11), nullable=False)
    passwd = Column(String(16), nullable=False)
    nick = Column(String(35))
    regtime = Column(String(10))
    isadmin = Column(Integer, nullable=False)
    face = Column(String(150))
    info = Column(String(200))

    # 便于查询数据库后转换为json数据
    def __str__(self):
        map_str = {'uid': self.uid, 'phonenum': self.phonenum, 'passwd': self.passwd, 'nick': self.nick,
                   'regtime': self.regtime, 'isadmin': self.isadmin, 'face': self.face, 'info': self.info}
        return map_str
